/**
 * @Copyright ®2015 Sinosoft Co. Ltd. All rights reserved.<br/>
 * 项目名称 : 电子商务云服务平台
 * 创建日期 : 2016年6月28日
 * 修改历史 : 
 *     1. [2016年6月30日]修改文件 by chihouliang
 * 添加Javadoc注释，更新代码规范，静态工具类增加private构造函数
 */
package com.gavinwind.inspay.common.util;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFColor;

import java.awt.*;
import java.io.FileOutputStream;
import java.util.List;

/**
 * Excel操作工具类
 * 
 * @date 2010-11-27
 * @since JDK 1.5
 * @author dingkui
 * @version 1.0
 * 
 */
public class ExcelUtil {

    private ExcelUtil() {}

    /**
     * 合并单元格
     * @param cSheet Excel的Sheet对象
     * @param cStartRow 
     *      起始行
     * @param cStartCol 
     *      起始列
     * @param cEndRow 
     *      结束行
     * @param cEndCol 
     *      结束列
     * @throws Exception 
     *      异常
     */
    public static void mergedRegion(Sheet cSheet, int cStartRow, int cStartCol, int cEndRow, int cEndCol) throws Exception {
        if (cSheet != null) {
            cSheet.addMergedRegion(new CellRangeAddress(cStartRow, cEndRow, cStartCol, cEndCol - 1));
        } else {
            throw new RuntimeException();
        }
    }

    /**
     * 设定某行的值
     * 
     * @param cSheet
     *            Excel的Sheet对象
     * @param cValues
     *            待写入的List
     * @param cRowNum
     *            行号
     */
    @SuppressWarnings("rawtypes")
    public static void getReportRow(Sheet cSheet, List cValues, int cRowNum) {
        if (cValues == null) {
            return;
        }
        Row tRow = cSheet.getRow(cRowNum);
        if (tRow == null) {
            tRow = cSheet.createRow(cRowNum);
        }
        for (int i = 0; i < cValues.size(); i++) {
            Cell tCell = tRow.getCell(i);
            if (tCell == null) {
                tCell = tRow.createCell(i);
            }
            tCell.setCellValue("" + (String) cValues.get(i));
        }
    }

    /**
     * 设定某单元格的值
     * 
     * @param cSheet
     *            Excel的Sheet对象
     * @param cValue
     *            待写入的String
     * @param cRowNum
     *            单元格行号
     * @param cColNum
     *            单元格列号
     */
    public static void getReportCell(Sheet cSheet, String cValue, int cRowNum, int cColNum) {
        Row tRow = cSheet.getRow(cRowNum);
        if (tRow == null) {
            tRow = cSheet.createRow(cRowNum);
        }
        Cell tCsCell = tRow.getCell(cColNum);
        if (tCsCell == null) {
            tCsCell = tRow.createCell(cColNum);
        }
        tCsCell.setCellValue("" + cValue);
    }

    /**
     * 生成excel文件
     * 
     * @param cWorkbook
     *            POI的workbook对象
     * @param cFileName
     *            文件名
     */
    public static void writeToFile(Workbook cWorkbook, String cFileName) {
        try {
            FileOutputStream tFileOutputStream = new FileOutputStream(cFileName);
            cWorkbook.write(tFileOutputStream);
            tFileOutputStream.close();
        } catch (Exception e) {
            throw new RuntimeException("保存Excel文件出错！");
        }
    }

    /**
     * 获取指定单元格上的值
     * 
     * @param cSheet
     *            cSheet
     * @param cRowNum
     *            Excel的行
     * @param cColNum
     *            Excel的列
     * @return 值对象
     */
    public static Object getValue(Sheet cSheet, int cRowNum, int cColNum) {
        Object tObj = null;

        if (cSheet != null) {
            Row tRow = cSheet.getRow(cRowNum);

            if (tRow != null) {
                Cell tCell = tRow.getCell(cColNum);

                if (tCell != null) {
                    int tCellType = tCell.getCellType();

                    if (tCellType == Cell.CELL_TYPE_NUMERIC) {
                        if (HSSFDateUtil.isCellDateFormatted(tCell)) {
                            tObj = tCell.getDateCellValue();
                            if (tObj == null) {
                                tObj = "";
                            }
                        } else {
                            try {
                                tObj = new Double(tCell.getNumericCellValue());
                            } catch (NumberFormatException ex) {
                                tObj = tCell.getDateCellValue();

                                return tObj;
                            }
                        }
                    } else if (tCellType == Cell.CELL_TYPE_STRING) {
                        tObj = String.valueOf(tCell.getStringCellValue());
                    } else if (tCellType == Cell.CELL_TYPE_FORMULA) {
                        tObj = String.valueOf(tCell.getNumericCellValue());
                    } else if (tCellType == Cell.CELL_TYPE_BLANK) {
                        tObj = tCell.getStringCellValue();
                    } else if (tCellType == Cell.CELL_TYPE_BOOLEAN) {
                        tObj = Boolean.valueOf(tCell.getBooleanCellValue());
                    } else {
                        tObj = "";
                    }
                }
            }
        }
        if (tObj instanceof String) {
            String tStr = (String) tObj;
            tStr = StringUtil.replace(tStr, "　", "").trim();
            tObj = tStr;
        }
        return tObj;
    }

    /**
     * 克隆字体
     * 
     * @param cFont
     *            要克隆的字体
     * @return 克隆的字体
     */
    public static Font cloneFont(Font cFont) {
        Workbook tWorkbook = new HSSFWorkbook();
        Font tNewFont = tWorkbook.createFont();
        tNewFont.setFontName(cFont.getFontName());
        tNewFont.setFontHeightInPoints(cFont.getFontHeightInPoints());
        tNewFont.setColor(cFont.getColor());
        tNewFont.setBoldweight(cFont.getBoldweight());
        tNewFont.setItalic(cFont.getItalic());
        tNewFont.setStrikeout(cFont.getStrikeout());
        tNewFont.setUnderline(cFont.getUnderline());
        return tNewFont;
    }

    /**
     * 取得执行的HSSFColor颜色
     * @param cColor 顏色
     * @return HSSF顏色
     */
    public static HSSFColor getHSSFColor(Color cColor) {
        HSSFWorkbook tHSSFWorkbook = new HSSFWorkbook();
        HSSFPalette tHSSFPalette = tHSSFWorkbook.getCustomPalette();
        HSSFColor tHSSFColor = tHSSFPalette.findColor((byte) cColor.getRed(), (byte) cColor.getGreen(), (byte) cColor.getBlue());
        if (tHSSFColor == null) {
            tHSSFColor = tHSSFPalette.addColor((byte) cColor.getRed(), (byte) cColor.getGreen(), (byte) cColor.getBlue());
        }
        return tHSSFColor;
    }

    /**
     * 取得执行的XSSFColor颜色
     * @param cColor 顏色
     * @return HSSF顏色
     */
    public static XSSFColor getXSSFColor(Color cColor) {
        final XSSFColor tXSSFColor = new XSSFColor();
        byte[] tRgbArray = new byte[3];
        tRgbArray[0] = (byte) cColor.getRed();
        tRgbArray[1] = (byte) cColor.getGreen();
        tRgbArray[2] = (byte) cColor.getBlue();
        tXSSFColor.setRgb(tRgbArray);
        return tXSSFColor;
    }

    /**
     * 
      * 新建sheet
      * 
      * @param cWorkbook
      *       参数
      * @param cSheetname
      *         参数
      * @return Sheet
     */
    public static Sheet createSheetForUpdate(Workbook cWorkbook, String cSheetname) {
        return cWorkbook.createSheet(cSheetname);
    }

}
